"""
Robot Framework library for connecting to MySQL

Uses Python DB API 2.0 Driver "PyMySQL"
See http://www.pymysql.org/ for more info.

"""

ROBOT_LIBRARY_VERSION = 0.1

import pymysql
from robot.utils import asserts

class mySQLdbLibrary:

    conn = None
    
    def connectToDb(self, host_url, username, password, db_name=None, port_number=3306):
        """ Connect to database
               
            Input:
                host_url      - Url to db host
                username      - User name of db user
                password      - Password of db user
                db_name       - Name of db
                port_number   - Port of db (default is 3306)

        """

        self.conn = pymysql.connect(host=host_url, port=port_number, user=username, passwd=password, db=db_name)


    def closeDbConnection(self):
        """ Commits changes and closes connection to db. 
            Assumes that connectToDb has been run first.
        """

        if self.conn is not None:
            self.conn.commit()
            self.conn.close()
        else:
            print '*WARN* No open connection to close'

    def executeQuery(self,sql_query):
        """ Executes a SQL query and returns the first object in the resultset (if any)
            Assumes that connection to db has been created with connectToDb.
            N.B. Currently also only returns one string, i.e. you can only specify one column to be returned. 
        """
        result = None
        
        if self.conn is not None:
            print 'Executing query: "' + sql_query +'"'
            cur = self.conn.cursor()
            cur.execute(sql_query)
            res = cur.fetchone()
            if res is not None:
                result = res[0]
            cur.close()
        else:
            asserts.fail('No open db connection! Please make sure to open connection before executing query.')

        return result
        
  
    def executeQueryAll(self,sql_query):
        """ Executes a SQL query and returns the first object in the resultset (if any)
            Assumes that connection to db has been created with connectToDb.
            N.B. Currently also only returns one string, i.e. you can only specify one column to be returned. 
        """
        result = None
        
        
        if self.conn is not None:
            cur = self.conn.cursor()
            cur.execute(sql_query)
            res = cur.fetchall()
            cur.close()
        else:
            asserts.fail('No open db connection! Please make sure to open connection before executing query.')

        return res    

